
** BUILD OUT BOND ISSUANCE PANEL BY GCELPARENT YEAR

use "../Raw/psedo/clean_bonds_issued", clear
tab type_of_security, sort

//cut down bond issuance to GCEL issuers
joinby companyid using ../Raw/SDC/sdc_gcel_company_link.dta
keep if ~missing(GCELcompanyID) //limit to GCEL Sample

 //clean security type
tab type_of_security, sort 

gen security_type_clean = "CP" if type_of_security == "CP"
replace security_type_clean = "Secured Bond" if inlist(type_of_security,"Secured Bonds","Sr Sec Bonds","Secured Debs","Sr Secured Nts","Debt Sec, Com","Debt Sec, Pfd","Gtd Sen Sec Nts")
replace security_type_clean = "MTN" if inlist(type_of_security,"Med-Trm Debs","Medium-Term Nts","Sr Med Term Nts","Gtd Mdm-Trm Nts","Mdm-Trm Fl Nts","Serial MTNs")
replace security_type_clean = "MTN" if regexm(type_of_security,"MTN")
replace security_type_clean = "Med-Trm" if regexm(type_of_security,"MTN")
replace security_type_clean = "Note" if (regexm(type_of_security,"Nt") |regexm(type_of_security,"Notes")) & security_type_clean == ""
replace security_type_clean = "Other" if inlist(type_of_security,"Islamic Finance","Preferred Shs","Preference Shs")
replace security_type_clean = "Other" if regexm(type_of_security,"Shs")
replace security_type_clean = "Other" if regexm(type_of_security,"Stk")
replace security_type_clean = "Bond" if missing(security_type_clean)

//merge in borrower_id
rename GCELParentID ParentID_GCEL 
merge m:1 ParentID_GCEL using ../Intermediate/borrower_file, keepusing(borrower_id) 
order borrower_id
drop ParentID_GCEL
drop if missing(sdc_id)

keep borrower_id sdc_id principal_amt issue_date security_type_clean coupon
duplicates drop 

//clean up file
egen transaction_bond_id = group(sdc_id)
rename principal_amt amount_trans_usdm 
rename issue_date date_trans 
rename security_type_clean debt_type

order borrower_id transaction_bond_id sdc_id date_trans amount_trans_usdm debt_type coupon 
compress
save ../Intermediate/transaction_clean_bonds, replace

//get number of bookrunners per bond issue
use ../Raw/SDC/clean_bonds_bookrunners_name, clear
gegen n_bookrunners_sdc = nvals(bookrunner), by(sdc_id)
keep sdc_id n_bookrunners_sdc
duplicates drop
//cut to gcel sample 
merge 1:m sdc_id using ../Intermediate/transaction_clean_bonds, keepusing(sdc_id) keep(3) nogen
duplicates drop 
tempfile nbrs 
save `nbrs'


//final build of transaction lender file
use ../Intermediate/transaction_clean_bonds, clear
drop borrower_id
duplicates drop
merge 1:1 sdc_id using `nbrs', keep(1 3) nogen
replace n_bookrunners_sdc = 0 if missing(n_bookrunners_sdc)
joinby sdc_id using "../Raw/SDC/SDC_Bank_Main_Bank", unmatched(master)
drop bookrunner name name_id
duplicates drop
tab _merge 
drop if _merge == 1
drop _merge 
egen n_bookrunner_linked = nvals(BankID), by(sdc_id)
bys sdc_id: gen first = _n == 1
tab n_b* if first == 1
sum n_boo* if first == 1, de 
drop first
gen float BankAllocation_pct = 100/n_bookrunner_linked
gen amount_trans_lender_usdm = amount_trans_usdm/n_bookrunner_linked

keep transaction_bond_id BankID BankAllocation_pct amount_trans_lender_usdm

compress
save ../Intermediate/transaction_lender_clean_bonds, replace




